﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;


    public class DB_Base_Helper
    {
    /// <summary>
    /// 字符中文枚举
    /// </summary>
    public enum DB_SQL_Select_Cols_Type
    {
        string_二进制模式 = 0,
        string_部分列名模式 = 1,
        string_全部列名模式 = 2,
    }

 
    public static string AutoCreateSelectSql<T>(string strWhere, string colsstring, DB_SQL_Select_Cols_Type dB_SQL_Select_Cols_Type)
        {
            Type type = typeof(T);
            if (dB_SQL_Select_Cols_Type == DB_SQL_Select_Cols_Type.string_二进制模式)
        {
                if (string.IsNullOrEmpty(strWhere)) strWhere = "1=1";
                PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>();
                List<bool> bools = StringHelper.LetStringtoBoolList(colsstring);
                List<string> DBColumnName = new List<string>();
                if (bools.Count != properties.Length)
                    return "";
                int index = 0;
                foreach (PropertyInfo property in properties)
                {
                    if (bools[index++])
                    DBColumnName.Add(String.Format("[{0}]", property.Name));

                }
                string colstext = StringHelper.GetAccumulationString(DBColumnName, ",", false);
                string sql = $"SELECT {colstext} FROM [{AttributeHelper.GetTName(type)}] WHERE {strWhere}";
                return sql;
        }
        else if (dB_SQL_Select_Cols_Type == DB_SQL_Select_Cols_Type.string_部分列名模式)
        {
            if (string.IsNullOrEmpty(strWhere)) strWhere = "1=1";
            List<string> strings = StringHelper.GetSplitListString(colsstring,",",false);
            List<string> strings_plus = new List<string>();
            foreach (string property in strings)
            {
                strings_plus.Add(String.Format("[{0}]", property));
            }
            string colstext = StringHelper.GetAccumulationString(strings_plus, ",", false);
            string sql = $"SELECT {colstext} FROM [{AttributeHelper.GetTName(type)}] WHERE {strWhere}";
            return sql;
        }
        else if (dB_SQL_Select_Cols_Type == DB_SQL_Select_Cols_Type.string_全部列名模式)
        {
            if (string.IsNullOrEmpty(strWhere)) strWhere = "1=1";
            if (colsstring == "*")
            {
                string sql = $"SELECT {colsstring} FROM [{AttributeHelper.GetTName(type)}] WHERE {strWhere}";
                return sql;
            }
            else
            {
                string sql = $"SELECT * FROM [{AttributeHelper.GetTName(type)}] WHERE {strWhere}";
                return sql;
            }
        }
        else
        {
            return "";
        }
         


        }

    /// <summary>
    /// 生成Delete语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="strWhere"></param>
    /// <returns></returns>
    public static string AutoCreateDeleteSql<T>(string strWhere)
    {
        Type type = typeof(T);
        string sql = $"DELETE FROM [{AttributeHelper.GetTName(type)}] WHERE ";
        if (!string.IsNullOrEmpty(strWhere))
            sql += strWhere;
        else
            sql += "1=1";
        return sql;
    }

    /// <summary>
    /// 生成假删除语句  update
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="strWhere"></param>
    /// <param name="isDelete"></param>
    /// <returns></returns>
    public static string AutoCreateLogicDeleteSql<T>(string strWhere, int isDelete)
    {
        Type type = typeof(T);
        string sql = $"Update [{AttributeHelper.GetTName(type)}] set IsDeleted={isDelete} WHERE 1=1";
        if (!string.IsNullOrEmpty(strWhere))
            sql += " and " + strWhere;
        return sql;
    }

    /// <summary>
    /// 生成Insert语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="cols">要插入的必要列</param>
    /// <returns></returns>
    public static string AutoCreateInsertSqlAndParas<T>(T t, string cols,ref List<SqlParameter> arrParas,bool UpdateContainPrimaryKey)
    {
        Type type = typeof(T);
        PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
        string sql = "";
        if (UpdateContainPrimaryKey)
        {
            string columns = string.Join(",", properties.Select(p => $"[{AttributeHelper.GetColName(p)}]"));
            //生成插入的参数{2}
            string paraColumns = string.Join(",", properties.Select(p => $"@{AttributeHelper.GetColName(p)}"));
            //参数数组的生成
            arrParas = AutoCreateParameters<T>(properties, t);
            //sql语句
            sql = $"INSERT INTO [{AttributeHelper.GetTName(type)}] ({columns}) VALUES ({paraColumns}) ";
        }
        else
        {
            string priName = AttributeHelper.GetPrimary(type);//获取主键名   标识列（不需要显式插入）
                                               //生成要插入的列 {1}  insert into table (Id,Name,Age....) values (@Id,@Name,@Age)
            string columns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => $"[{AttributeHelper.GetColName(p)}]"));
            //生成插入的参数{2}
            string paraColumns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => $"@{AttributeHelper.GetColName(p)}"));
            //参数数组的生成
            arrParas = AutoCreateParameters<T>(properties, t);
            //sql语句
            sql = $"INSERT INTO [{AttributeHelper.GetTName(type)}] ({columns}) VALUES ({paraColumns}) ";
        }
        return sql;

    }

    /// <summary>
    /// 生成Update语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <param name="cols">包括主键名</param>
    /// <returns></returns>
    public static string AutoCreateUpdateSqlAndParas<T>(T t, string cols, string strWhere, ref List<SqlParameter> arrParas, bool UpdateContainPrimaryKey)
    {
        Type type = typeof(T);
        //获取所有属性
        string sql = "";
        if (UpdateContainPrimaryKey)
        {
            //获取所有属性
            PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
            string priName =   AttributeHelper.GetPrimary(type);
            //生成要更新的列 {1}   update 表名 set col1=@col1,col2=@col2,.....   where 条件
            string columns = string.Join(",", properties.Select(p => string.Format("[{0}]=@{0}", AttributeHelper.GetColName(p))));
            ;            //参数数组的生成
             arrParas = AutoCreateParameters<T>(properties, t);

            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = $"{priName}=@{priName}";
            }

            //sql语句
            sql = $"UPDATE [{AttributeHelper.GetTName(type)}] SET {columns} WHERE {strWhere}";
            return sql;

        }
        else
        {

            //获取所有属性
            PropertyInfo[] properties = PropertyHelper.GetTypeProperties<T>(cols);
            string priName = AttributeHelper.GetPrimary(type);
            //生成要更新的列 {1}   update 表名 set col1=@col1,col2=@col2,.....   where 条件
            string columns = string.Join(",", properties.Where(p => p.Name != priName).Select(p => string.Format("[{0}]=@{0}", AttributeHelper.GetColName(p))));
            ;            //参数数组的生成
            arrParas = AutoCreateParameters<T>(properties, t);

            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = $"{priName}=@{priName}";
            }

            //sql语句
            sql = $"UPDATE [{AttributeHelper.GetTName(type)}] SET {columns} WHERE {strWhere}";
            return sql;
        }


    }


    //生成参数数组
    public static List<SqlParameter> AutoCreateParameters<T>(PropertyInfo[] properties, T t)
    {
        SqlParameter[] arrParas = properties.Select(p => new SqlParameter("@" + AttributeHelper.GetColName(p), p.GetValue(t) ?? DBNull.Value)).ToArray();
        return arrParas.ToList();
    }
}